package db;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.Map;
import java.util.TreeMap;

import javax.crypto.KeyGenerator;
import javax.servlet.http.HttpServletRequest;
import javax.swing.JOptionPane;



import classes.Event;
import classes.Game;
import classes.Ranking;
import classes.Street;
import classes.User;
import com.google.gson.Gson;



public class DB {

	static {
		final String driver="org.sqlite.JDBC";
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException notFound) {
			System.err.println("CRITICAL ERROR>>Could not find driver '"+driver+"'.\n\t\tData-base, will not function!");
			System.err.println("\t\tAborting run! Shutting-down application!");			
			System.exit(1);
		}

	}

	public static final String DB_PATH="UsersDB.db";
	public Connection conn;
	public static final int STREET_PRICE=1000;
	public static final int STREET_PANELTY=1000;
	private static boolean firstTime=true;


	public DB() throws SQLException {
		conn=DriverManager.getConnection("jdbc:sqlite:"+DB_PATH);
		Statement ValidateTable=conn.createStatement();
		// ************* clearing DB *******************
		
		
		/**
		ValidateTable.executeUpdate("DROP TABLE IF EXISTS User;");	
		System.out.println("DB>>> Table 'User' successfully droped");
		ValidateTable.executeUpdate("DROP TABLE IF EXISTS OwnedStreets;");
		System.out.println("DB>>> Table 'OwnedStreets' successfully droped");
		ValidateTable.executeUpdate("DROP TABLE IF EXISTS Games;");
		System.out.println("DB>>> Table 'Games' successfully droped");
		//ValidateTable.executeUpdate("DROP TABLE IF EXISTS streets;");
		//System.out.println("DB>>> Table 'streets' successfully droped");
		ValidateTable.executeUpdate("DROP TABLE IF EXISTS events;");
		System.out.println("DB>>> Table 'events' successfully droped");
		 **/
		try {
			ValidateTable.executeUpdate(
					"CREATE TABLE User(userName,password,firstName,lastName,money,color,game_unique_key,last_loc);"
			);
			System.out.println("DB>>> Table 'User' successfully created");
		} catch (SQLException alreadyExists) {
			System.out.println("DB>>> Table 'User' already exists");
		}		
		try {
			ValidateTable.executeUpdate("CREATE TABLE Games(game_unique_key,num_players,max_players,start_money,start_time);");
			System.out.println("DB>>> Table 'Games' successfully created");
		} catch (SQLException alreadyExists) {
			System.out.println("DB>>> Table 'Games' already exists");
		}		
		ValidateTable.close();     
		try {
			ValidateTable.executeUpdate("CREATE TABLE OwnedStreets(owner_username,street,game_unique_key);");
			System.out.println("DB>>> Table 'OwnedStreets' successfully created");
		} catch (SQLException alreadyExists) {
			System.out.println("DB>>> Table 'OwnedStreets' already exists");
		}
		try {
			ValidateTable.executeUpdate("CREATE TABLE streets(street,game_unique_key,price,panelty,open_for_sale,coordinates);");
			System.out.println("DB>>> Table 'streets' successfully created");
		} catch (SQLException alreadyExists) {
			System.out.println("DB>>> Table 'street' already exists");
		}
		try {
			ValidateTable.executeUpdate("CREATE TABLE events(userName,message,messageType);");
			System.out.println("DB>>> Table 'events' successfully created");
		} catch (SQLException alreadyExists) {
			System.out.println("DB>>> Table 'events' already exists");
		}

		try{
			//testData();
		}catch(Exception e){
			System.out.println(e.toString());
		}
		ValidateTable.close();  
	}

	public void close() throws SQLException{
		conn.close();
	}

	public synchronized Connection getConnection(){
		try{
			if (!conn.isValid(70000))
				conn=DriverManager.getConnection("jdbc:sqlite:"+DB_PATH);
		}catch(Exception e){
			System.out.println("problem init connection to db");
		}
		return conn;
	}
	private void testData() throws SQLException {

	}


	public synchronized void printDB() throws SQLException {
		Statement usersQuery=conn.createStatement();		
		ResultSet users=usersQuery.executeQuery("SELECT * FROM User;");
		System.out.println();
		System.out.println("\tuserName\tpassword\tfirstName\tlastname\tmoney");
		System.out.println("====================================================================================");
		while (users.next()) {
			System.out.println("\t"+users.getString(1)+"\t"+users.getString(2)+"\t"+users.getString(3)+"\t"+users.getString(4)+"\t"+users.getString(5)+"\t"+users.getString(6)+"\t"+users.getString(7)+"\t"+users.getString(8));
		}
		System.out.println();

		Statement streetsQuery=conn.createStatement();		
		ResultSet streetsSet=streetsQuery.executeQuery("SELECT * FROM OwnedStreets;");
		System.out.println();
		System.out.println("\towner\tstreet\t\tunique_game_key");
		System.out.println("====================================================================================");
		while (streetsSet.next()) {
			System.out.println("\t"+streetsSet.getString(1)+"\t\t"+streetsSet.getString(2)+"\t\t"+streetsSet.getString(3)+"\t\t");
		}
		System.out.println();

		streetsQuery=conn.createStatement();		
		streetsSet=streetsQuery.executeQuery("SELECT * FROM streets;");
		System.out.println();

		System.out.println("\t"+"streetName"+"\t"+"unique_game_key"+"\t"+"price"+"\t"+"panelty"+"\t"+"open");
		System.out.println("====================================================================================");
		while (streetsSet.next()) {
			System.out.println("\t"+streetsSet.getString(1)+"\t\t"+streetsSet.getString(2)+"\t\t"+streetsSet.getString(3)+"\t\t"+streetsSet.getString(4)+"\t\t"+streetsSet.getString(5)+"\t\t"+streetsSet.getString(6));
		}
		System.out.println();

		streetsQuery=conn.createStatement();		
		streetsSet=streetsQuery.executeQuery("SELECT * FROM Games;");
		System.out.println();

		System.out.println("/t/tgame_unique_key/t/tnum_players/t/tmax_players/t/tstart_money/t/tstart_time");
		System.out.println("====================================================================================");
		while (streetsSet.next()) {
			System.out.println("\t"+streetsSet.getString(1)+"\t\t"+streetsSet.getString(2)+"\t\t"+streetsSet.getString(3)+"\t\t"+streetsSet.getString(4)+"\t\t"+streetsSet.getString(5));
		}
		System.out.println();

		streetsQuery=conn.createStatement();		
		streetsSet=streetsQuery.executeQuery("SELECT * FROM events;");
		System.out.println();

		System.out.println("events");
		System.out.println("====================================================================================");
		while (streetsSet.next()) {
			System.out.println("\t"+streetsSet.getString(1)+"\t\t"+streetsSet.getString(2)+"\t\t"+streetsSet.getString(3)+"\t\t");
		}
		System.out.println();

		System.out.println();

		//		streetsQuery=conn.createStatement();		
		//		//streetsSet=streetsQuery.executeQuery("SELECT * FROM streetNamess;");
		//		System.out.println();
		//
		//		System.out.println("\t"+"streetNameHeb"+"\t"+"streetNameEng");
		//		System.out.println("====================================================================================");
		//		while (streetsSet.next()) {
		//			System.out.println("\t"+streetsSet.getString(1)+"\t\t"+streetsSet.getString(2));
		//		}


	}





	public synchronized String registerUser(String userName,String password,String firstName,String lastName) throws SQLException{
		PreparedStatement numberOfUsersWithUsername=conn.prepareStatement(
				"SELECT COUNT(*) FROM User WHERE userName = ?;"
		);
		numberOfUsersWithUsername.setString(1, userName);		
		ResultSet rs=numberOfUsersWithUsername.executeQuery();

		int count=0;        
		if (rs.next())
			count=rs.getInt(1);   
		rs.close();
		numberOfUsersWithUsername.close();
		if (count>0)
			return "user name already exist" ;

		PreparedStatement insertNewUser = conn.prepareStatement(
				"INSERT INTO User values (?,?,?,?,?,?,?,?);"
		);
		insertNewUser.setString(1, userName);
		insertNewUser.setString(2, password);
		insertNewUser.setString(3, firstName);
		insertNewUser.setString(4, lastName);
		insertNewUser.setString(5, null);
		insertNewUser.setString(6, null);
		insertNewUser.setString(7, null);
		insertNewUser.setString(8, null);
		//		conn.setAutoCommit(false);
		insertNewUser.executeUpdate();
		//		conn.setAutoCommit(true);
		insertNewUser.close();
		//		if(firstTime){
		//			testData();
		//			firstTime=false;
		//		}
		printDB();
		return "200";

	}

	public synchronized String loginUser(String userName,String password, HttpServletRequest request) throws SQLException  {
		PreparedStatement userStat=conn.prepareStatement(
				"SELECT * FROM User WHERE userName=? AND password=?;"
		);
		userStat.setString(1, userName);
		userStat.setString(2, password);

		ResultSet rs=userStat.executeQuery();
		int count=0;
		User user = new User();
		if (rs.next()){
			user.setUserName(rs.getString("userName"));
			user.setPassword(rs.getString("password"));
			user.setFirstName(rs.getString("firstName"));
			user.setLastName(rs.getString("lastName"));
			user.setMoney(rs.getString("money"));
			user.setUniqueKey(rs.getString("game_unique_key"));
			user.setColor(rs.getString("color"));
			count++;
		}
		rs.close();
		userStat.close();
		if(count!=1)            
			return "300";
		Gson gson = new Gson();
		String ownedStreets= getOwnedStreetForUser(user);
		user.setOwnedStreets(ownedStreets);
		LinkedList<String> otherPlayersData=getotherPlayersData(user);
		otherPlayersData.addFirst(gson.toJson(user));
		String[] allUserDataInStringArray = new String[otherPlayersData.size()];
		otherPlayersData.toArray(allUserDataInStringArray);
		String allUserDataInOneString= gson.toJson(allUserDataInStringArray);
		return allUserDataInOneString;
	}

	private synchronized LinkedList<String> getotherPlayersData(User user) throws SQLException {
		PreparedStatement otherPlayers=conn.prepareStatement("SELECT * FROM User WHERE game_unique_key=?;");
		otherPlayers.setString(1, user.getUniqueKey());
		ResultSet rs=otherPlayers.executeQuery();
		LinkedList<String> otherP=new LinkedList<String>();
		Gson gson = new Gson();
		while(rs.next()){
			User other= new User();
			other.setUserName(rs.getString("userName"));
			other.setPassword(rs.getString("password"));
			other.setFirstName(rs.getString("firstName"));
			other.setLastName(rs.getString("lastName"));
			other.setMoney(rs.getString("money"));
			other.setUniqueKey(rs.getString("game_unique_key"));
			String ownedStreets= getOwnedStreetForUser(other);
			other.setOwnedStreets(ownedStreets);
			otherP.add(gson.toJson(other));	
		}

		return otherP;
	}

	private synchronized String getOwnedStreetForUser(User obj) throws SQLException {
		PreparedStatement userStreets=conn.prepareStatement(
				//"SELECT street FROM OwnedStreets WHERE owner_username=? AND game_unique_key=?;"
				"SELECT street FROM OwnedStreets WHERE owner_username=? ;"
		);

		userStreets.setString(1, obj.getUserName());
		//		userStreets.setString(2, obj.getUniqueKey());
		ResultSet rs=userStreets.executeQuery();
		LinkedList<String> streets=new LinkedList<String>();
		while (rs.next()){
			//JOptionPane.showMessageDialog(null,rs.getString("street") );
			streets.add(rs.getString("street"));	
		}
		String[] streetsAsArray=new String[streets.size()];
		streets.toArray(streetsAsArray);
		Gson gson=new Gson();
		String ret=gson.toJson(streetsAsArray);	
		System.out.println("The streets: "+ret);
		return ret;

	}

	public synchronized User getUserByUserName(String userName) throws SQLException{
		PreparedStatement userStat=conn.prepareStatement("SELECT * FROM User WHERE userName=?;");
		userStat.setString(1, userName);
		ResultSet rs=userStat.executeQuery();
		User user = new User();
		if (rs.next()){
			user.setUserName(rs.getString("userName"));
			user.setPassword(rs.getString("password"));
			user.setFirstName(rs.getString("firstName"));
			user.setLastName(rs.getString("lastName"));
			user.setMoney(rs.getString("money"));
			user.setUniqueKey(rs.getString("game_unique_key"));
			user.setColor(rs.getString("color"));
			user.setLast_loc(rs.getString("last_loc"));
		}
		rs.close();
		userStat.close();
		return (user);	
	}
	public synchronized void transfareMoney(User paying,User beingPayed,int money) throws SQLException{
		PreparedStatement payingPerp= conn.prepareStatement("Update user Set money=? WHERE userName=?;");
		int payerMoney=(Integer.parseInt(paying.getMoney())-money);
		payingPerp.setString(1, payerMoney+"");
		payingPerp.setString(2, paying.getUserName());		
		PreparedStatement beingPayedPerp= conn.prepareStatement("Update user Set money=? WHERE userName=?;");
		int beingPayedMoney=(Integer.parseInt(beingPayed.getMoney())+money);
		beingPayedPerp.setString(1,beingPayedMoney+"");
		beingPayedPerp.setString(2,beingPayed.getUserName());	

		//conn.setAutoCommit(false);
		beingPayedPerp.executeUpdate();
		beingPayedPerp.close();
		payingPerp.executeUpdate();
		payingPerp.close();
		//conn.setAutoCommit(true);

		payingPerp.close();
		beingPayedPerp.close();
	}

	public synchronized String handleChangeLocation(String userLocation, String userName, String gameKey) throws SQLException {
		//String message="";
		User user=getUserByUserName(userName);
		PreparedStatement userStat= conn.prepareStatement("SELECT * FROM ownedStreets WHERE owner_username<>? AND street=? AND game_unique_key=?;");
		userStat.setString(1, userName);
		userStat.setString(2, userLocation);
		userStat.setString(3, user.getGame_unique_key());
		System.out.println("***Last Location: "+user.getLast_loc());
		System.out.println("***Game Unique key: "+user.getGame_unique_key());
		System.out.println("***Current Location: "+userLocation);
		ResultSet rs=userStat.executeQuery();
		if (rs.next()) {
			System.out.println("******************DROPED ON OWNED STREET******");
			if(!userLocation.equals(user.getLast_loc())){ // the street Owned by a player, and it the first time the user drop on it
				String ownerName=rs.getString("owner_username");
				User streetOwner=getUserByUserName(ownerName);
				PreparedStatement userStat2= conn.prepareStatement("SELECT * FROM streets WHERE street=? ;");
				userStat2.setString(1, userLocation);
				//userStat2.setString(2, gameKey);
				ResultSet streetP=userStat2.executeQuery();
				if(streetP.next()){
					System.out.println("******************PAYING******");
					int streetPanelty=Integer.parseInt(streetP.getString("panelty"));
					transfareMoney(user,streetOwner,streetPanelty);	
					String messagePayer="You have entered "+userLocation+" street,"+ '\n'+
					"which belongs to "+ownerName+"."+'\n'+" As a result you have payed "+streetPanelty+" Shekels";
					String messageOwner=user.getFirstName()+" payed you "+streetPanelty+" NIS "+'\n'+" for dropping on "+userLocation+" street";
					insertMessage(userName,messagePayer,"PAY");
					insertMessage(streetOwner.getUserName(),messageOwner,"COLLECT");
					
				}
				userStat2.close();
				streetP.close();
		}
		userStat.close();
		}
		if(!userLocation.equals(user.getLast_loc())){
			System.out.println("*********Last Location UPDATE************");
			PreparedStatement changeLastLoc=conn.prepareStatement("Update user Set last_loc=? where userName=?;");
			changeLastLoc.setString(1,userLocation);
			changeLastLoc.setString(2,userName);
			changeLastLoc.executeUpdate();
			changeLastLoc.close();
		}
		//		rs.close();
		//		userStat.close();
		//		System.out.println(user);
		//		if(!streetisOwned){
		//			userStat= conn.prepareStatement("SELECT * FROM streets WHERE street=? AND game_unique_key=? AND open_for_sale=?;");
		//			userStat.setString(1, userLocation);
		//			//userStat.setString(2,"0000");
		//			userStat.setString(2,user.getUniqueKey());
		//			userStat.setString(3, "open");
		//			rs=userStat.executeQuery();
		//			if(rs.next()){
		//				message="You have reached "+userLocation+" and it is open for sale";
		//			}
		//			rs.close();
		//			userStat.close();
		//		}
		return "OK";
	}




	public synchronized String getAvailGames() throws SQLException {
		//game_unique_key,city,location,num_of_players,initMoney
		PreparedStatement availGames= conn.prepareStatement("SELECT * FROM Games ;");
		ResultSet rs=availGames.executeQuery();
		LinkedList<String> games=new LinkedList<String>();
		while(rs.next()){

			int maxPlayers = rs.getInt("max_players");
			int countPlayers = rs.getInt("num_players");
			String count= rs.getString("num_players");
			System.out.println("max:"+maxPlayers);
			System.out.println("count:"+countPlayers);
			System.out.println("count2:"+count);
			if (maxPlayers!=countPlayers){
				Game game =new Game();
				Gson gson = new Gson();
				game.setKey(rs.getString("game_unique_key"));
				game.setMaxNumOfPlayers(maxPlayers);
				game.setNumOfPlayers(countPlayers);
				game.setStartMoney(rs.getInt("start_money"));
				long startTime = rs.getLong(("start_time"));
				long curTime = System.currentTimeMillis();
				game.setRunningTime(curTime-startTime);
				games.add(gson.toJson(game));
			}
		}
		String[] gamesAsStringArr=new String[games.size()];
		games.toArray(gamesAsStringArr);
		Gson gson=new Gson();
		String gamesAsString = gson.toJson(games);
		return gamesAsString;
	}

	public synchronized void insertStreet(String streetName,String game_unique_key,String price, String panelty, String open,String coordinates) throws SQLException{
		PreparedStatement already = conn.prepareStatement("SELECT * from streets where street=? ");
		already.setString(1, streetName);
		ResultSet rs= already.executeQuery();
		if(rs.next()){
			return;
		}

		PreparedStatement streets = conn.prepareStatement("INSERT INTO streets values (?, ?, ?,?,?,?);");
		streets.setString(1,streetName);
		streets.setString(2,game_unique_key);
		streets.setString(3,price);
		streets.setString(4,panelty);
		streets.setString(5,open);
		streets.setString(6,coordinates);
		//conn.setAutoCommit(false);
		streets.executeUpdate();
		//conn.setAutoCommit(true);

	}

	public synchronized void insertOwnedStreet(String userName,String streetName,String game_unique_key) throws SQLException{
		//		System.out.println("giving  "+streetName+" to "+userName );
		//		
		PreparedStatement already = conn.prepareStatement("SELECT * from ownedStreets where owner_username=? and street=? ");
		already.setString(1,userName);
		already.setString(2,streetName);
		ResultSet rs= already.executeQuery();
		if(rs.next()){
			return;
		}


		PreparedStatement streets = conn.prepareStatement("INSERT INTO ownedStreets values (?,?,?);");
		streets.setString(1,userName);
		streets.setString(2,streetName);
		streets.setString(3,game_unique_key);
		System.out.println(streets);
		//conn.setAutoCommit(false);
		streets.executeUpdate();
		//conn.setAutoCommit(true);

	}

	public synchronized String openNewGame(String numOfPlayers, String initMoney,String userName) throws SQLException {
		//game_unique_key,city,location,num_of_players
		User user=getUserByUserName(userName);
		if (user.getUniqueKey()!=null)
			return "You are already signed up to another game!";
		PreparedStatement newGame = conn.prepareStatement("INSERT INTO Games values (?,?,?,?,?);");
		newGame.setString(1,userName); //key
		newGame.setInt(2,1);//numOfPlayers
		newGame.setInt(3,Integer.parseInt(numOfPlayers));//maxNumOfPlayers
		newGame.setInt(4,Integer.parseInt(initMoney));//startMoney
		newGame.setLong(5,System.currentTimeMillis());//startTime
		//conn.setAutoCommit(false);
		newGame.executeUpdate();
		//conn.setAutoCommit(true);
		newGame.close();

		PreparedStatement joinGame= conn.prepareStatement("Update user Set game_unique_key=?, money=?,color=? WHERE userName=?;");
		joinGame.setString(1,userName);
		joinGame.setString(2,initMoney);
		joinGame.setString(3,"1");
		joinGame.setString(4,userName);	
		//		conn.setAutoCommit(false);
		joinGame.executeUpdate();
		//		conn.setAutoCommit(true);
		joinGame.close();
		return "OK";
	}

	public synchronized String getPlayerRanking(String userName) throws SQLException {
		User user= getUserByUserName(userName);
		System.out.println("ranking: "+userName);
		PreparedStatement userStat=conn.prepareStatement(
				"SELECT * FROM User WHERE game_unique_key=? Order By money DESC;"
		);
		userStat.setString(1,user.getUniqueKey());
		ResultSet rs=userStat.executeQuery();
		LinkedList<String> players=new LinkedList<String>();

		Gson gson= new Gson();
		while(rs.next()){
			Ranking rank = new Ranking();
			rank.setFirstName(rs.getString("firstName"));
			rank.setLastName(rs.getString("lastName"));
			rank.setMoney(rs.getString("money")) ;
			players.add(gson.toJson(rank));
		}
		userStat.close();
		rs.close();
		String[] rankingArray= new String[players.size()];
		players.toArray(rankingArray);
		return gson.toJson(rankingArray);
	}

	public synchronized String joinGame(String gameUniqueKey,String userName) throws SQLException {

		PreparedStatement moneyQ=conn.prepareStatement(
				"SELECT * FROM Games WHERE game_unique_key=?;"
		);
		moneyQ.setString(1, gameUniqueKey);
		ResultSet moneySet= moneyQ.executeQuery();
		if (moneySet==null)
			return "The initial money value in game you have chosen is not set yet";
		moneySet.next();
		String initMoney = Integer.toString(moneySet.getInt("start_money"));
		System.out.println("money: "+initMoney);
		int currPlayersNum=moneySet.getInt("num_players");

		moneySet.close();		

		PreparedStatement userStat=conn.prepareStatement(
				"SELECT * FROM User WHERE userName=? and game_unique_key=?;"
		);
		userStat.setString(1,userName);
		userStat.setString(2,null);
		ResultSet rs=userStat.executeQuery();
		if(rs.next()){
			return "You are already sign up to another game";	
		}

		//ResultSet rs=userStat.executeQuery();
		//while(rs.next()){
		//	String game_unique_key=rs.getString("game_unique_key");
		//	if (game_unique_key==null){
		System.out.println("*********JOINING NEW PLAYER**********");
		PreparedStatement joinGame= conn.prepareStatement("Update user Set game_unique_key=?, money=?, color=? WHERE userName=?;");
		joinGame.setString(1, gameUniqueKey);
		joinGame.setString(2, initMoney);
		joinGame.setString(3, Integer.toString((currPlayersNum+1)));
		joinGame.setString(4, userName);
		//conn.setAutoCommit(false);
		joinGame.executeUpdate();
		//conn.setAutoCommit(true);
		joinGame.close();
		PreparedStatement playersnum=conn.prepareStatement("UPDATE games Set num_players=? where game_unique_key=?;");
		playersnum.setString(1, Integer.toString((currPlayersNum+1)));
		playersnum.setString(2,gameUniqueKey);
		//conn.setAutoCommit(false);
		playersnum.executeUpdate();
		printDB();
		return "OK";

		//conn.setAutoCommit(true);
		//return "OK";	
		//}else{
		//	return "You are already sign up to another game";	
		//	}		
		//}
		//return "Sorry, it looks like we have a problem retriving your info from our resources. plz try again";

	}

	public synchronized String update(String userName,String password, HttpServletRequest request) throws SQLException  {
		User user=getUserByUserName(userName);
		if(user==null)            
			return "300";
		Gson gson = new Gson();
		String ownedStreets= getOwnedStreetForUser(user);
		System.out.println(ownedStreets);
		user.setOwnedStreets(ownedStreets);
		LinkedList<String> otherPlayersData=getotherPlayersData(user);
		otherPlayersData.addFirst(gson.toJson(user));
		String[] allUserDataInStringArray = new String[otherPlayersData.size()];
		otherPlayersData.toArray(allUserDataInStringArray);
		String allUserDataInOneString= gson.toJson(allUserDataInStringArray);
		return allUserDataInOneString;
	}



	public synchronized String sellStreet(String userName, int streetNum) throws SQLException {
		System.out.println("sell street: "+streetNum);
		User user=getUserByUserName(userName);
		PreparedStatement sellPerp= conn.prepareStatement("Update user Set money=? WHERE userName=?;");
		int sellerMoney=(Integer.parseInt(user.getMoney())+STREET_PRICE);
		String str=Integer.toString(sellerMoney);
		System.out.println(str);
		sellPerp.setString(1, str);
		sellPerp.setString(2, userName);		
		sellPerp.executeUpdate();
		sellPerp.close();

		PreparedStatement sellPerp2= conn.prepareStatement("Select * from ownedStreets where owner_username=? ;");
		sellPerp2.setString(1, userName);
		ResultSet rs=sellPerp2.executeQuery();
		int counter=0;
		String street="";
		while(rs.next() && counter<=streetNum ){
			street=rs.getString("street");
			counter++;
		}
		rs.close();
		sellPerp2.close();

		PreparedStatement sellPerp3= conn.prepareStatement("DELETE from ownedStreets WHERE street=? and owner_username=? ;");
		sellPerp3.setString(1, street);
		sellPerp3.setString(2, userName);		
		sellPerp3.executeUpdate();
		sellPerp3.close();
		return street;




	}
	/**
	 * Checks if user location is owend by a player if so returs the owning user
	 * If user location is not owend decrease money and insert it into owned streets and returns "sold".  
	 * @param userLocation - the street's name
	 * @param userName - the buyer
	 * @param uniqueKey - 
	 * @return sold/userName/ failure
	 * @throws SQLException 
	 */
	public synchronized String buyStreet(String userLocation, String userName,String uniqueKey) throws SQLException {
		printDB();
		User user=getUserByUserName(userName);
		if(user==null)
			return "Problem getting your info from our DB";
		System.out.println(userName+" is buying "+userLocation);
		PreparedStatement already = conn.prepareStatement("SELECT * from ownedStreets where street=? and game_unique_key=? ");
		already.setString(1,userLocation);
		already.setString(2,user.getUniqueKey());
		ResultSet rs= already.executeQuery();
		if(rs.next()){
			return rs.getString("owner_username");
		}

		insertOwnedStreet(userName,userLocation,uniqueKey);
		//insertOwnedStreet(user.getUserName(), userLocation, user.getUniqueKey());
		PreparedStatement payingPerp= conn.prepareStatement("Update user Set money=? WHERE userName=?;");
		int playerMoney=Integer.parseInt(user.getMoney());
		System.out.println(playerMoney);
		playerMoney=playerMoney-STREET_PRICE;
		System.out.println("money after buy: "+playerMoney);
		payingPerp.setString(1, Integer.toString(playerMoney));
		payingPerp.setString(2, userName);
		payingPerp.executeUpdate();
		insertMessage(userName,"You have bought "+userLocation+" for "+STREET_PRICE+"NIS","BUY");
		return "sold";
	}

	public synchronized void insertMessage(String userName, String msg,String msgType) throws SQLException {
		PreparedStatement userMsg = conn.prepareStatement("insert into events values(?,?,?) ; ");
		userMsg.setString(1,userName);
		userMsg.setString(2,msg);
		userMsg.setString(3,msgType);

		userMsg.executeUpdate();

	}

	public synchronized String colorMap(String userName) throws SQLException {
		User user = getUserByUserName(userName);
		PreparedStatement userStat=conn.prepareStatement("SELECT * FROM ownedStreets,user,streets where ownedStreets.game_unique_key=? and ownedStreets.owner_username=user.userName and streets.street=ownedStreets.street  ;");
		userStat.setString(1, user.getUniqueKey());
		ResultSet rs=userStat.executeQuery();
		ArrayList<String> result= new ArrayList<String>();
		while (rs.next()){
			String playerColor= rs.getString("color");
			String coordinates=rs.getString("coordinates");
			Street streets=new Street();
			streets.setCoordinates(coordinates);
			streets.setPlayerColor(playerColor);
			Gson gson=new Gson();
			result.add(gson.toJson(streets));
		}
		userStat.close();
		rs.close();
		String[] streetsArray=new String[result.size()];
		result.toArray(streetsArray);
		Gson gson=new Gson();
		return gson.toJson(streetsArray);
	}

	public synchronized String[] getEventsForUser(String userName) throws SQLException {
		PreparedStatement userEvt=conn.prepareStatement("SELECT * FROM events where userName=? ;");
		userEvt.setString(1, userName);
		ResultSet rs=userEvt.executeQuery();
		Gson gson = new Gson();
		ArrayList<String> events= new ArrayList<String>();
		printDB();
		while (rs.next()){
			Event e =new Event();
			e.setMsg(rs.getString("message"));
			e.setMsgType(rs.getString("messageType"));
			events.add(gson.toJson(e));
		}
		String [] eventsArray;
		if(events.size()<10)
			eventsArray=new String[events.size()];
		else
			eventsArray=new String[10];

		for(int i=0;i<eventsArray.length;i++){
			eventsArray[i]=events.get(events.size()-i-1);
		}
		return eventsArray;


	}

}

//	public int getUserColor(String userName) throws SQLException {
//		PreparedStatement userStat=conn.prepareStatement("SELECT * FROM User WHERE userName=? ;");
//		userStat.setString(1, userName);
//		ResultSet rs=userStat.executeQuery();
//		if (rs.next()){
//			return rs.getInt("color");
//			
//		}
//		return -1;
//	}





